package me.gacl.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;


import me.gacl.utils.JdbcUtils;

/**
* @ClassName: TransactionDemo1
* @Description: 
* JDBC中使用事务来模似转帐 
    create table account(
        id int primary key auto_increment,
        name varchar(40),
        money float
    );
    insert into account(name,money) values('A',1000);
    insert into account(name,money) values('B',1000);
    insert into account(name,money) values('C',1000);
* @author: 孤傲苍狼
* @date: 2014-9-22 下午11:16:17
*
*/ 
public class TransactionDemo1 {
	
    /**
    * @throws SQLException 
     * @Method: testTransaction1
    * @Description: 模拟转账成功时的业务场景
    * @Anthor:孤傲苍狼
    *
    */ 
	
	
	@Test
	public void testTransaction1() throws SQLException {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		conn = JdbcUtils.getConnection();
		boolean defaultCommit = conn.getAutoCommit();
		System.out.println("defaultCommit:"+defaultCommit);
		try {
			
			conn.setAutoCommit(false);//通知数据库开启事务(start transaction)
		    
			String sql1 = "update account set money=money-100 where name='A'";
			st = conn.prepareStatement(sql1);
			st.executeUpdate();
			
			String sql2 = "update account set money=money+100 where name='B'";
		    st = conn.prepareStatement(sql2);
		    st.executeUpdate();
		    
		    conn.commit();//上面的两条SQL执行Update语句成功之后就通知数据库提交事务(commit)
		    System.out.println("成功！！！");  //log4j
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
		}finally {
			//conn.setAutoCommit(defaultCommit);
			JdbcUtils.release(conn, st, rs);
		}
	}
	
    /**
    * @throws SQLException 
     * @Method: testTransaction1
    * @Description: 模拟转账过程中出现异常导致有一部分SQL执行失败后让数据库自动回滚事务
    * @Anthor:孤傲苍狼
    *
    */ 
    @Test
    public void testTransaction2() throws SQLException{
    		Connection conn = null;
    		PreparedStatement st = null;
    		ResultSet rs = null;
    		try {
				conn = JdbcUtils.getConnection();
				boolean defaultCommit = conn.getAutoCommit();
				System.out.println("defaultCommit:"+defaultCommit);
				
				conn.setAutoCommit(false);//通知数据库开启事务(start transaction)
				
				String sql1 = "update account set money=money-100 where name='A'";
				st = conn.prepareStatement(sql1);
				st.executeUpdate();
				
				//用这句代码模拟执行完SQL1之后程序出现了异常而导致后面的SQL无法正常执行，事务也无法正常提交，此时数据库会自动执行回滚操作
				int x = 1/0;
				String sql2 = "update account set money=money+100 where name='B'";
	            st = conn.prepareStatement(sql2);
	            st.executeUpdate();
	            conn.commit();//上面的两条SQL执行Update语句成功之后就通知数据库提交事务(commit)
	            System.out.println("成功！！！");
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}finally {
				JdbcUtils.release(conn, st, rs);
			}
    }
    
    
    /**
    * @Method: testTransaction1
    * @Description: 模拟转账过程中出现异常导致有一部分SQL执行失败时手动通知数据库回滚事务
    * @Anthor:孤傲苍狼
    *
    */ 
    @Test
    public void testTransaction3(){
    		Connection conn = null;
    		PreparedStatement st = null;
    		ResultSet rs = null;
    		try {
				conn = JdbcUtils.getConnection();
				conn.setAutoCommit(false);//通知数据库开启事务(start transaction)
				String sql1 = "update account set money=money-100 where name = 'A'";
				st = conn.prepareStatement(sql1);
				st.executeUpdate();
				//用这句代码模拟执行完SQL1之后程序出现了异常而导致后面的SQL无法正常执行，事务也无法正常提交，此时数据库会自动执行回滚操作
				int x = 1/0;
	            String sql2 = "update account set money=money+100 where name='B'";
	            st = conn.prepareStatement(sql2);
	            st.executeUpdate();
	            conn.commit();//上面的两条SQL执行Update语句成功之后就通知数据库提交事务(commit)
	            System.out.println("成功！！！");
			} catch (Exception e) {
				// TODO: handle exception
				 try {
		                //捕获到异常之后手动通知数据库执行回滚事务的操作
		                conn.rollback();
		            } catch (SQLException e1) {
		                e1.printStackTrace();
		            }
				e.printStackTrace();
			}finally {
				JdbcUtils.release(conn, st, rs);
			}
    }
    
	
	
	
}
